from pyexcelerate import Workbook, Fill, Color, Style


def cell_coloring_by_pyexcelerate(path, data):
    wbk = Workbook()
    data_to_save = [data.columns.tolist(), ] + data.values.tolist()
    workbook_Sheet = wbk.new_sheet('Sheet1', data=data_to_save)
    # 色塗り
    fill = Fill(background=Color(255, 0, 0, 0))
    index_length = len(data.index)
    map_name = dict(zip(data.columns.tolist(), range(len(data.columns))))
    for index, row in data.iteritems():
        for one in range(1, index_length, 2):
            if row[one] != row[one - 1]:
                workbook_Sheet.set_cell_style(one + 2, map_name.get(index) + 1, Style(fill=fill))
    wbk.save(path)


def to_xlsx(filename, df):
    data = [list(df.columns)] + df.values.tolist()
    pairs = [list(zip(a[0], a[1])) for a in list(zip(data[1::2], data[2::2]))]
    diffs = []
    for i, p in enumerate(pairs):
        row = 3 + i * 2
        result = [i for i, pp in zip(range(len(p)), p) if pp[0] != pp[1]]
        diffs += ([(row, col + 1) for col in result])
    
    wb = Workbook()
    ws = wb.new_sheet("Diffs", data=data)
    fill = Fill(background=Color(255, 145, 186, 0))
    [ws.set_cell_style(cell[0], cell[1], Style(fill=fill)) for cell in diffs]
    wb.save(filename)
